package cfca.seal.dao.main.impl;

import cfca.seal.bean.Seal;
import cfca.seal.bean.User;
import cfca.seal.common.Page;
import cfca.seal.dao.PageDao;
import cfca.seal.dao.SmartDao;
import cfca.seal.dao.main.IUserDao;
import cfca.seal.exception.CodeException;
import cfca.seal.system.Env;
import cfca.seal.util.StringUtil;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.annotation.Resource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

@Component
public class UserDao
  implements IUserDao
{
  private static String tableName = "用户";
  private static final Logger log = LoggerFactory.getLogger("web_sys");

  @Resource
  private SmartDao smartDao;

  @Resource
  private PageDao pageDao;

  public boolean insert(User user) throws CodeException { try { String sql = "INSERT INTO USERS (ID, NAME, REAL_NAME, CERT_SN, CERT_DN, REMARK, ORGANIZATION_ID, CREATE_TIME) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

      Object[] args = { user.getId(), user.getName(), user.getRealName(), user.getCertSn(), user.getCertDn(), user.getRemark(), user.getOrganizationId(), new Date() };

      log.debug("insert user sql: " + sql + args);
      int i = this.smartDao.update(sql, args);
      return i > 0;
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1002", tableName + "表插入失败", e);
    } }

  public boolean update(User user) throws CodeException
  {
    try
    {
      String sql = "UPDATE USERS SET NAME = ?, REAL_NAME = ?, CERT_SN = ?, CERT_DN = ?, ORGANIZATION_ID = ?, REMARK = ? WHERE ID = ?";

      Object[] args = { user.getName(), user.getRealName(), user.getCertSn(), user.getCertDn(), user.getOrganizationId(), user.getRemark(), user.getId() };

      log.debug("update user sql by id: " + sql + args);
      int i = this.smartDao.update(sql, args);
      return i > 0;
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1003", tableName + "表更新失败", e);
    }
  }

  public boolean deleteById(String id) throws CodeException
  {
    try {
      String sql = "DELETE FROM USERS WHERE ID = ?";
      Object[] args = { id };
      log.debug("delete user sql by id: " + sql + args);
      int i = this.smartDao.update(sql, args);
      return i > 0;
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1004", tableName + "表删除失败", e);
    }
  }

  public User getUserById(String id) throws CodeException
  {
    try {
      String sql = "SELECT * FROM USERS WHERE ID = ?";
      Object[] args = { id };
      log.debug("select user sql by id: " + sql + args);
      return (User)this.smartDao.getBean(User.class, sql, args);
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1001", tableName + "表检索失败", e);
    }
  }

  public User getUserByDN(String dn) throws CodeException
  {
    try {
      String sql = "SELECT * FROM USERS WHERE CERT_DN = ?";
      Object[] args = { dn };
      log.debug("select user sql by certDN: " + sql + args);
      return (User)this.smartDao.getBean(User.class, sql, args);
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1001", tableName + "表检索失败", e);
    }
  }

  public List<User> getUserByCertDnAndCertSn(String certDN, String certSN) throws CodeException
  {
    try {
      String sql = "SELECT * FROM USERS WHERE CERT_DN = ? AND CERT_SN = ?";
      Object[] args = { certDN, certSN };
      log.debug("select user sql by CertDnAndCertSn: " + sql + args);
      return this.smartDao.getList(User.class, sql, args);
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1001", tableName + "表检索失败", e);
    }
  }

  public List<User> getUserByCertSn(String certSN) throws CodeException
  {
    try {
      String sql = "SELECT * FROM USERS WHERE CERT_SN = ?";
      Object[] args = { certSN };
      log.debug("select user sql by CertSn: " + sql + args);
      return this.smartDao.getList(User.class, sql, args);
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1001", tableName + "表检索失败", e);
    }
  }

  public List<User> getAll(String orgid) throws CodeException
  {
    try {
      String sql = "SELECT * FROM USERS";
      if ((orgid != null) && (!orgid.isEmpty())) {
        sql = sql + " WHERE ORGANIZATION_ID = '" + orgid + "'";
      }
      log.debug("select user sql by orgid: " + sql);
      return this.smartDao.getList(User.class, sql);
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1001", tableName + "表检索失败", e);
    }
  }

  public List<User> getBindUser(String sealid) throws CodeException
  {
    try {
      String sql = "SELECT * FROM USERS INNER JOIN SEAL_USER ON USERS.ID = SEAL_USER.ID WHERE SEAL_USER.ID = ?";
      Object[] args = { sealid };
      log.debug("select user sql by id join seal_user: " + sql + args);
      return this.smartDao.getList(User.class, sql, args);
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1001", tableName + "表检索失败", e);
    }
  }

  public List<Seal> getBindedSeals(String userId) throws CodeException
  {
    try {
      String sql = "SELECT * FROM SEAL WHERE SEAL.ID IN ( SELECT SEAL_ID FROM SEAL_USER WHERE USER_ID = ?)";
      Object[] args = { userId };
      log.debug("select seal sql by userId: " + sql + args);
      return this.smartDao.getList(Seal.class, sql, args);
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1001", "用户表/印章表/用户印章关联表表检索失败", e);
    }
  }

  public boolean isBinding(String sealid, String userid) throws CodeException
  {
    try {
      String sql = "SELECT COUNT(*) FROM SEAL_USER WHERE ID = ? AND USER_ID = ?";
      Object[] args = { sealid, userid };
      log.debug("select count seal_user sql: " + sql + args);
      int count = this.smartDao.getCount(sql, args);
      return count > 0;
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1001", tableName + "表检索失败", e);
    }
  }

  public User getUserByName(String name) throws CodeException
  {
    try {
      String sql = "SELECT * FROM USERS WHERE NAME = ?";
      Object[] args = { name };
      log.debug("select user sql by id: " + sql + args);
      return (User)this.smartDao.getBean(User.class, sql, args);
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1001", tableName + "表检索失败", e);
    }
  }

  public Page<User> getPage(User user, String pageNo, String orgid) throws CodeException
  {
    try {
      List conditionList = new ArrayList();
      List args = new ArrayList();

      if (StringUtil.isNotEmpty(orgid)) {
        conditionList.add("ORGANIZATION_ID = ?");
        args.add(orgid);
      }

      String name = user.getName();
      if (StringUtil.isNotEmpty(name)) {
        conditionList.add("NAME like ?");
        args.add("%" + name + "%");
      }

      String realName = user.getRealName();
      if (StringUtil.isNotEmpty(realName)) {
        conditionList.add("REAL_NAME like ?");
        args.add("%" + realName + "%");
      }

      String certSn = user.getCertSn();
      if (StringUtil.isNotEmpty(certSn)) {
        conditionList.add("CERT_SN = ?");
        args.add(certSn);
      }

      String certDn = user.getCertDn();
      if (StringUtil.isNotEmpty(certDn)) {
        conditionList.add("CERT_DN = ?");
        args.add(certDn);
      }

      String startTime = user.getStartTime();
      if (StringUtil.isNotEmpty(startTime)) {
        if ("sqlite".equals(Env.databaseType)) {
          conditionList.add("DATETIME(substr(CREATE_TIME,0,11), 'unixepoch', 'localtime') >= DATETIME(?)");
          args.add(startTime);
        } else if ("mysql".equals(Env.databaseType)) {
          conditionList.add("CREATE_TIME >= date_format(?,'%y-%m-%d %H:%i:%s')");
          args.add(startTime);
        } else if ("oracle".equals(Env.databaseType)) {
          conditionList.add("CREATE_TIME >= to_date(?,'yyyy-mm-dd HH24:MI:SS')");
          args.add(startTime);
        } else if ("db2".equals(Env.databaseType)) {
          conditionList.add("CREATE_TIME >= to_date(?,'yyyy-mm-dd hh24:mi:ss')");
          args.add(startTime);
        }
      }

      String endTime = user.getEndTime();
      if (StringUtil.isNotEmpty(endTime)) {
        if ("sqlite".equals(Env.databaseType)) {
          conditionList.add("DATETIME(substr(CREATE_TIME,0,11), 'unixepoch', 'localtime') <= DATETIME(?)");
          args.add(endTime);
        } else if ("mysql".equals(Env.databaseType)) {
          conditionList.add("CREATE_TIME <= date_format(?,'%y-%m-%d %H:%i:%s')");
          args.add(endTime);
        } else if ("oracle".equals(Env.databaseType)) {
          conditionList.add("CREATE_TIME <= to_date(?,'yyyy-mm-dd HH24:MI:SS')");
          args.add(endTime);
        } else if ("db2".equals(Env.databaseType)) {
          conditionList.add("CREATE_TIME <= to_date(?,'yyyy-mm-dd hh24:mi:ss')");
          args.add(endTime);
        }
      }

      String organizationId = user.getOrganizationId();
      if (StringUtil.isNotEmpty(organizationId)) {
        conditionList.add("ORGANIZATION_ID = ?");
        args.add(organizationId);
      }

      return this.pageDao.dispage(User.class, "USERS", conditionList, args.toArray(), pageNo, 20);
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1001", tableName + "表检索失败", e);
    }
  }

  public List<User> getUserByCertSnForUpdate(String certSn, String id) throws Exception
  {
    try {
      String sql = "SELECT * FROM USERS WHERE CERT_SN = ? AND ID <> ?";
      Object[] args = { certSn, id };
      log.debug("select user sql by getUserByCertSnForUpdate: " + sql + args);
      return this.smartDao.getList(User.class, sql, args);
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("D1001", tableName + "表检索失败", e);
    }
  }
}